/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.deployment.dao;

import br.com.deployment.daogenerico.DaoGenerico;
import br.com.deployment.entidade.humana.Pessoa;
import br.com.deployment.entidade.protocolo.Pagamento;
import br.com.deployment.entidade.protocolo.Parcela;
import br.com.deployment.entidade.protocolo.Protocolo;
import br.com.deployment.entidade.protocolo.ServicoProtocolo;
import br.com.deployment.entidade.selo.Selo;
import br.com.deployment.interfaces.ProtocoloDaoIn;
import br.com.deployment.utilitario.Msg;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.NoResultException;
import javax.persistence.Query;

/**
 *
 * @author roniere
 */
@Stateless
@LocalBean
public class ProtocoloDao extends DaoGenerico<Protocolo> implements ProtocoloDaoIn{

    public ProtocoloDao() {
        super(Protocolo.class);
    }
    
    @Override
     public int salvar(Protocolo entity) {
        getEm().persist(entity);
        return entity.getId();
    }

    @Override
    public List<Protocolo> buscar(Protocolo protocolo) {
        String sql = "select p from Protocolo p where";
        if (protocolo.getId() != 0) {
            sql += " p.id=:id AND";
        }

        if (protocolo.getCliente() != null) {
            if (protocolo.getCliente().getNome() != null) {
                sql += " lower(p.cliente.nome) like lower(:nome) AND";
            }
        }

        if (protocolo.getDataCad() != null) {
            sql += " p.dataCad=:data AND";
        }
        sql += " 1=1";

        Query query = getEm().createQuery(sql);

        if (protocolo.getId() != 0) {
            query.setParameter("id", protocolo.getId());
        }

        if (protocolo.getCliente() != null) {
            if (protocolo.getCliente().getNome() != null) {
                query.setParameter("nome", protocolo.getCliente().getNome());
            }
        }

        if (protocolo.getDataCad() != null) {
            query.setParameter("data", protocolo.getDataCad());
        }

        return query.getResultList();

    }

    @Override
    public List<ServicoProtocolo> tipoSeloProtocolo(Selo selo) {
        List<ServicoProtocolo> lista = new ArrayList<ServicoProtocolo>();
        String sql = " Select s from Protocolo p JOIN p.servicoProtocolos s JOIN s.selos se where  ";

        if (selo.getSerie() != null && selo.getSerie() != "") {
            sql += " lower(se.serie) like lower(:serie) and ";
        }
        if (selo.getNumero() != 0) {
            sql += "  se.numero = :num and ";
        }

        if (selo.getTipoSelo() != null) {
            sql += " se.tipoSelo.id = :id and ";
        }


        sql += " 1=1 ";

        Query query = getEm().createQuery(sql);


        if (selo.getSerie() != null && selo.getSerie() != "") {
            query.setParameter("serie", "" + selo.getSerie() + "");
        }

        if (selo.getNumero() != 0) {
            query.setParameter("num", selo.getNumero());
        }

        if (selo.getTipoSelo() != null) {
            query.setParameter("id", selo.getTipoSelo().getId());
        }

        lista = query.getResultList();

        return lista;
    }

    @Override
    public List<ServicoProtocolo> servicoProtocolos(Protocolo p) {
        String sql = "select sp from Protocolo c JOIN c.servicoProtocolos sp where"
                + " c.id = :id";
        Query query = getEm().createQuery(sql);
        query.setParameter("id", p.getId());

        return query.getResultList();
    }

    @Override
    public List<Protocolo> recentes() {
        String sql = "Select p from Protocolo p where "
                + " p.dataCad BETWEEN :antes AND :depois"
                + " ORDER BY p.id DESC";
        Query query = getEm().createQuery(sql).setMaxResults(5);
        query.setParameter("antes", new Date());
        query.setParameter("depois", new Date());
        return query.getResultList();
    }

    @Override
    public List<Protocolo> pendentesPagamento() {
        String sql = "Select p from Protocolo p JOIN p.parcelas pa where"
                + " pa.ativo = false and 0 <"
                + " (Select count(par) from Protocolo pr JOIN pr.parcelas par where "
                + " pr.id = p.id and par.ativo = true) ORDER BY p.id DESC";
        Query query = getEm().createQuery(sql);
        return query.getResultList();
    }

    @Override
    public List<Protocolo> pendentesSelo() {
//        String sql = "Select DISTINCT(p) from Protocolo p JOIN p.servicoProtocolos sp where"
//                + " sp.id IN ( Select spi.id from Protocolo pi"
//                + "    JOIN pi.servicoProtocolos spi"
//                + " where spi.selos IS EMPTY and pi.id = p.id) ORDER BY p.id DESC";
        String sql = "Select DISTINCT(sp.protocolo) from ServicoProtocolo sp "
                + "  where sp.selos IS EMPTY ORDER BY sp.protocolo.id DESC";
        Query query = getEm().createQuery(sql);
        return query.getResultList();
    }

    @Override
    public List<Protocolo> pendentesPagamento(int p) {
        String sql = "Select p from Protocolo p JOIN p.parcelas pa where"
                + " pa.ativo = false and p.id = :id and 0 <"
                + " (Select count(par) from Protocolo pr JOIN pr.parcelas par where "
                + " pr.id = p.id and par.ativo = true) ORDER BY p.id DESC";
        Query query = getEm().createQuery(sql);
        query.setParameter("id", p);
        return query.getResultList();
    }

    @Override
    public List<Protocolo> pendentesSelo(int p) {
        String sql = "Select DISTINCT(p) from Protocolo p JOIN p.servicoProtocolos sp where"
                + " sp.id IN ( Select spi.id from Protocolo pi"
                + "    JOIN pi.servicoProtocolos spi"
                + " where spi.selos IS EMPTY and pi.id = p.id) and p.id = :id ORDER BY p.id DESC";
        Query query = getEm().createQuery(sql);
        query.setParameter("id", p);
        return query.getResultList();
    }

    @Override
    public List<Protocolo> finalizados() {
        String sql = "Select DISTINCT(p) from Protocolo p JOIN p.servicoProtocolos sp JOIN p.parcelas pa where"
                + " pa.ativo = false and"
                + " 0 = (Select count(par) from Protocolo pr JOIN pr.parcelas par where "
                + " pr.id = p.id and par.ativo = true) and "
                + " sp.id IN ( Select spi.id from Protocolo pi"
                + "    JOIN pi.servicoProtocolos spi"
                + " where spi.selos IS NOT EMPTY and pi.id = p.id)"
                + " ORDER BY p.id DESC";
        Query query = getEm().createQuery(sql).setMaxResults(10);
        return query.getResultList();
    }

    @Override
    public Protocolo getProtocoloToSelar(int id) {
        Protocolo lst = new Protocolo();
        String sql = "Select DISTINCT(p) from Protocolo p JOIN p.servicoProtocolos sp "
                + " where p.ativo = true and p.id = :id and"
                + " sp.id IN (Select spi.id from Protocolo pi"
                + "    JOIN pi.servicoProtocolos spi"
                + " where spi.selos IS EMPTY and pi.id = p.id)";


        Query query = getEm().createQuery(sql).setMaxResults(10);
        query.setParameter("id", id);

        try {
            lst = (Protocolo) query.getSingleResult();
        } catch (NoResultException e) {
            if (find(id) == null) {
                Msg.messagemError("Protocolo Inexistente");
                throw new RuntimeException("Protocolo Inexiste");
            } else {
                Msg.messagemError("Protocolo já foi Selado");
                throw new RuntimeException("Protocolo já foi Selado");
            }
        }

        return lst;
    }

    @Override
    public List<Parcela> getParcelas(int id) {
        String sql = "Select pa from Protocolo p JOIN p.parcelas pa where p.id = :id";
        Query query = getEm().createQuery(sql);
        query.setParameter("id", id);
        return query.getResultList();
    }

    @Override
    public void verificaPendencia(Protocolo p) {
    }
    
    @Override
     public List<Protocolo> relatorioTodos(Date dataInicial, Date dataFinal) {
        String sql = "Select p from Protocolo p JOIN p.parcelas pa where"
                +" p.dataCad BETWEEN :dataInicial AND :dataFinal ORDER BY p.id ASC";
        Query query = getEm().createQuery(sql);
        query.setParameter("dataInicial", dataInicial);
        query.setParameter("dataFinal", dataFinal);
        List<Protocolo> lst = query.getResultList();
        
        if(lst != null && !lst.isEmpty()){
            return lst;
        }else{
            lst = new ArrayList<Protocolo>();
        }
        
        return lst;
    }
     
    @Override
     public List<Protocolo> pendentesPagamento(Date dataInicial, Date dataFinal) {
        String sql = "Select p from Protocolo p JOIN p.parcelas pa where"
                + " pa.ativo = false and 0 <"
                + " (Select count(par) from Protocolo pr JOIN pr.parcelas par where "
                + " pr.id = p.id and par.ativo = true)"
                + " and p.dataCad BETWEEN :dataInicial AND :dataFinal ORDER BY p.id ASC";
        Query query = getEm().createQuery(sql);
        query.setParameter("dataInicial", dataInicial);
        query.setParameter("dataFinal", dataFinal);
        return query.getResultList();
    }

    @Override
    public List<Protocolo> pendentesSelo(Date dataInicial, Date dataFinal) {
        String sql = "Select DISTINCT(p) from Protocolo p JOIN p.servicoProtocolos sp where"
                + " sp.id IN ( Select spi.id from Protocolo pi"
                + "    JOIN pi.servicoProtocolos spi"
                + " where spi.selos IS EMPTY and pi.id = p.id)"
                + " and p.dataCad BETWEEN :dataInicial AND :dataFinal "
                + " ORDER BY p.id ASC";
        Query query = getEm().createQuery(sql);
        query.setParameter("dataInicial", dataInicial);
        query.setParameter("dataFinal", dataFinal);
        return query.getResultList();
    }
    
    @Override
     public List<Protocolo> finalizados(Date dataInicial, Date dataFinal) {
        String sql = "Select DISTINCT(p) from Protocolo p JOIN p.servicoProtocolos sp JOIN p.parcelas pa where"
                + " pa.ativo = false and"
                + " 0 = (Select count(par) from Protocolo pr JOIN pr.parcelas par where "
                + " pr.id = p.id and par.ativo = true) and "
                + " sp.id IN ( Select spi.id from Protocolo pi"
                + "    JOIN pi.servicoProtocolos spi"
                + " where spi.selos IS NOT EMPTY and pi.id = p.id)"
                + " and p.dataCad BETWEEN :dataInicial AND :dataFinal "
                + " ORDER BY p.id ASC";
        Query query = getEm().createQuery(sql).setMaxResults(10);
        query.setParameter("dataInicial", dataInicial);
        query.setParameter("dataFinal", dataFinal);
        return query.getResultList();
    }
     
    @Override
      public List<Protocolo> pesquisaPessoaProtocolo(Pessoa pessoa) {
        List<Protocolo> lista = new ArrayList<Protocolo>();
        String sql = " Select p from Protocolo p JOIN p.cliente c where c.id = :id";
        Query query = getEm().createQuery(sql);
        query.setParameter("id",pessoa.getId());
        lista = query.getResultList();
        System.err.println(sql);

        return lista;
    }

    
    @Override
     public List<Protocolo> relatorioProtocoloData(Date data) {
        List<Protocolo> lista = new ArrayList<Protocolo>();
        String sql = " Select p from Protocolo p JOIN p.servicoProtocolos s JOIN s.selos se "
                   + " where p.dataCad = :data ";
        Query query = getEm().createQuery(sql);
        
        query.setParameter("data",data);
        lista = query.getResultList();
        System.err.println(sql);

        return lista;
    }
     
    
    @Override
    public List<ServicoProtocolo> relatorioSeloProtocolo(Date data, Date dataFinal){
        List<ServicoProtocolo> lista = new ArrayList<ServicoProtocolo>();
        String sql = " Select DISTINCT(s) from ServicoProtocolo s JOIN s.selos se "
                   + " where se.protocolado = true and se.dataProtocolado BETWEEN :data AND :dataFinal";

        Query query = getEm().createQuery(sql);        
        query.setParameter("data", data);
        query.setParameter("dataFinal", dataFinal);
        System.err.println(data + "->JQPL>"+ query.getResultList());
        lista = query.getResultList();

        return lista;
    }   
    @Override
    public List<Protocolo> IntervaloSeloProtocolo(Selo selo, int num1, int num2) {
        List<Protocolo> lista = new ArrayList<Protocolo>();
        String sql = " Select p from Protocolo p JOIN p.servicoProtocolos s JOIN s.selos se where  ";

        if (selo.getSerie() != null && selo.getSerie() != "") {
            sql += " lower(se.serie) like lower(:serie) and ";
        }
       
        if (selo.getTipoSelo() != null) {
            sql += " se.tipoSelo.id = :id and ";
        }
        
        if (selo.getNumero() !=0) {
            sql += "  se.numero between :num1 and :num2 and";
        }

        
        sql += " 1=1 ";

        Query query = getEm().createQuery(sql);


        if (selo.getSerie() != null && selo.getSerie() != "") {
            query.setParameter("serie", "" + selo.getSerie() + "");
        }

        if (selo.getNumero() != 0) {
            query.setParameter("num1", num1);
            query.setParameter("num2", num2);
            
        }

        if (selo.getTipoSelo() != null) {
            query.setParameter("id", selo.getTipoSelo().getId());
        }

        lista = query.getResultList();

        return lista;
    }
    
    @Override
     public List<Pagamento> pagamentosData(Date data) {
        String sql = "select pa from Pagamento pa where pa.dataPag =:d ";
        Query query = getEm().createQuery(sql);
        query.setParameter("d", data);
        return query.getResultList();
    }
    
    @Override
    public List<Protocolo> protocolosData(Date data) {
        String sql = "select p from Protocolo p where p.dataCad =:d ";
        Query query = getEm().createQuery(sql);
        query.setParameter("d", data);
        return query.getResultList();
    }

    
    @Override
    public BigDecimal protocoloCaixaMes(Date dataini, Date datafim) {
        String sql = "select SUM(sp.valor) from ServicoProtocolo sp JOIN sp.protocolo p where"
                + " p.dataCad BETWEEN :dataini AND :datafim ";
        Query query = getEm().createQuery(sql);
        
        query.setParameter("dataini", dataini);
        query.setParameter("datafim", datafim);
        return (BigDecimal) query.getSingleResult();
    }
    
    @Override
    public BigDecimal protocoloCaixaMesCartorio(Date dataini, Date datafim) {
        String sql = "select SUM(sp.valorCartorio) from ServicoProtocolo sp JOIN sp.protocolo p where"
                + " p.dataCad BETWEEN :dataini AND :datafim ";
        Query query = getEm().createQuery(sql);
        
        query.setParameter("dataini", dataini);
        query.setParameter("datafim", datafim);
        return (BigDecimal) query.getSingleResult();
    }
    
    @Override
    public BigDecimal protocoloCaixaMesFerm(Date dataini, Date datafim) {
        String sql = "select SUM(sp.valorFermojupi) from ServicoProtocolo sp JOIN sp.protocolo p where"
                + " p.dataCad BETWEEN :dataini AND :datafim ";
        Query query = getEm().createQuery(sql);
        
        query.setParameter("dataini", dataini);
        query.setParameter("datafim", datafim);
        return (BigDecimal) query.getSingleResult();
    }
    
    @Override
    public BigDecimal protocoloCaixaMesBig(Date dataini, Date datafim) {
        String sql = "Select SUM(pa.valorPago) from Protocolo p JOIN p.parcelas pa "
                + " where pa.dataPag BETWEEN :dataini AND :datafim AND pa.ativo=false ";
        Query query = getEm().createQuery(sql);
        query.setParameter("dataini", dataini);
        query.setParameter("datafim", datafim);
        BigDecimal total = (BigDecimal)query.getSingleResult();
        return total;
    }
    @Override
    public BigDecimal protocoloCaixaMesReceberBig(Date dataini, Date datafim) {
        String sql = "Select SUM(pa.valorAPagar) from Protocolo p "
                + " JOIN p.parcelas pa "
                + " where pa.ativo = false ";
        Query query = getEm().createQuery(sql);
        query.setParameter("dataini", dataini);
        query.setParameter("datafim", datafim);
        BigDecimal total = (BigDecimal)query.getSingleResult();
        return total;
    }
    
    @Override
     public BigDecimal caixaAreceber(Date dataInicial, Date dataFinal){
        String sql = " select SUM(pa.valorAPagar) from Protocolo p "
                + " JOIN p.parcelas pa where "
                + " pa.ativo=TRUE and "
                + " p.dataCad BETWEEN :dataini AND :datafim ";
        
        Query query = getEm().createQuery(sql);
        query.setParameter("dataini", dataInicial);
        query.setParameter("datafim", dataFinal);
        BigDecimal total = (BigDecimal) query.getSingleResult();
        return total;
    }
     
    @Override
      public BigDecimal caixaRecebido(Date dataInicial, Date dataFinal){
        String sql = "select SUM(pa.valorPago) from Protocolo p "
                + " JOIN p.parcelas pa "
                + " where pa.ativo=false and "
                + " p.dataCad BETWEEN :dataini AND :datafim";
        
        Query query = getEm().createQuery(sql);
        query.setParameter("dataini", dataInicial);
        query.setParameter("datafim", dataFinal);
        BigDecimal total = (BigDecimal)query.getSingleResult();
        System.err.println(" -- "+ total);
        return total;
    }
      
    @Override
     public List<Protocolo> pesquisarCaixaProtocolo(Date dataInicial, Date dataFinal) { 
        String sql = "Select p from Protocolo p where p.dataCad BETWEEN :dataInicial AND :dataFinal ";
        Query query = getEm().createQuery(sql);
        query.setParameter("dataInicial", dataInicial);
        query.setParameter("dataFinal", dataFinal);
        return query.getResultList();  
      }
      
    @Override
      public List<Pagamento> pesquisarCaixaPagamento(Date dataInicial, Date dataFinal) { 
        String sql = "Select p from Pagamento p where p.dataPag BETWEEN :dataInicial AND :dataFinal ";
        Query query = getEm().createQuery(sql);
        query.setParameter("dataInicial", dataInicial);
        query.setParameter("dataFinal", dataFinal);
        return query.getResultList();  
      }

      
}
